<?php
ini_set("display_errors", 1);
error_reporting(E_ALL);
require_once 'MDB/QueryTool.php';
//$DB_BACKEND = 'pgsql';
$DB_BACKEND = 'mysql';
if ($DB_BACKEND == 'mysql') {
    $dbDSN = 'mysql://localhost@localhost/test';
    define ('TABLE_USER',   'user');
    define ('TABLE_TIME',   'time');
}
if ($DB_BACKEND == 'pgsql') {
    $dbDSN = "pgsql://test:test@/test";
    define ('TABLE_USER',   'uuser');     // user is a reserved word in postgres
}
class user extends MDB_QueryTool
{
    var $table =        TABLE_USER;
    var $sequenceName = TABLE_USER;
    var $primaryCol =   'userid';
    var $tableSpec = array(
                            array('name'    =>  TABLE_USER, 'shortName' =>  'user')
                        );
}
$user = new user($dbDSN, array('errorCallback'=>'myPrint'), 2);
echo "<pre>";
var_dump($user);
//$res = $user->save(array("username"=>"ffff"));
//myPrint($res);
echo "dd".$user->lastInsertID();
exit;
headline('$user->get(3)');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
$res = $user->get(3);
myPrint($res);
// query: SELECT * FROM user WHERE id=3
headline('$user->getAll()');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
$res = $user->getAll();
myPrint($res);
// query: SELECT * FROM user
// get the first 10 entries from the table (LIMIT 0,10)
headline('$user->getAll(0,10)');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
$res = $user->getAll(0,10);
myPrint($res);
// query: SELECT * FROM user LIMIT 0,10
// get all data where the id>3
headline('using setWhere');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
$user->setWhere('id>3');
$res = $user->getAll();
myPrint($res);
// query: SELECT * FROM user WHERE id>3
// setting multiple query-parts
headline('using set[Where,Order,Group]');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
// for proper SQL92 i think we need to select only the col we use in group
// in mysql you can also leave out the following line
$user->setSelect('name');
$user->setWhere('name IS NOT NULL');
$user->setOrder('name');
$user->setGroup('name');
$res = $user->getAll();
myPrint($res);
// query: SELECT * FROM user WHERE name IS NOT NULL GROUP BY name ORDER BY name
// setting multiple query-parts
headline('using setIndex');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
$user->setIndex('name');
$res = $user->getAll();
myPrint($res);
// query: SELECT * FROM user
// the result-array is indexed by 'name'
// setting multiple query-parts
headline('using setIndex using 2 columns!');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
$user->setIndex('name,surname');
$res = $user->getAll();
myPrint($res);
// query: SELECT * FROM user
// the result-array is indexed by 'name,surname'
// join the table 'time' this automatically detects where there are
// columns that refer to one another, this uses a regExp that you can simply
// modify (see $_tableNamePreg and $_columnNamePreg), by default this maps
// stuff like 'user_id'=>'user.id'
headline('using autoJoin');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
$user->autoJoin('time');
$user->setOrder('surname');
$res = $user->getAll();
myPrint($res);
// query: SELECT * FROM user,time WHERE user.id=time.user_id ORDER BY surname
// does the same as the example above
// only that you have to hardcode the join by hand, no autoJoin here
headline('using setJoin instead of autoJoin');
$user->reset();     // reset the query-builder, so no where, order, etc. are set
$user->setJoin('time',TABLE_USER.'.id=time.user_id');
$user->setOrder('surname');
$res = $user->getAll();
myPrint($res);
// query: SELECT * FROM user,time WHERE user.id=time.user_id ORDER BY surname
headline('adding data using $user->save($data)');
$data = array('login'=>'new','name'=>'foo','surname'=>'bar');
$fooBarId = $user->save($data);
myPrint($fooBarId);
// query: INSERT INTO user (id,login,name,surname) VALUES (<sequences>,"new","foo","bar")
headline('updating using $user->save($data)');
$data = array('id'=>$fooBarId,'login'=>'NEW','name'=>'Mr. foo');
$res = $user->save($data);
myPrint($res);
// query: UPDATE user (id,login,name,surname) VALUES (<sequences>,"new","foo","bar")
headline('updating using $user->update($data)');
$res = $user->update($data);
myPrint($res);
// query: UPDATE user (id,login,name,surname) VALUES (<sequences>,"new","foo","bar")
headline("remove the entry \$user->remove($fooBarId)");
$res = $user->remove($fooBarId);
myPrint($res);
// query: DELETE FROM "uuser" WHERE "id" = $fooBarId
function headline( $string='' )
{
    global $headlineCnt;
    if( $string )
    {
        $headlineCnt++;
        print "<h1>$headlineCnt - $string</h1>";
    }
}
function myPrint($data)
{
    print "<pre>";
    print_r($data);
    print '</pre>';
}
?>